import _imports  # 只在此项目有效，其他项目，请去掉此 import
import pybi as pbi
import pandas as pd
from src.data.dataset import load_df


styles = pbi.styles
easy_chart = pbi.easy_echarts

# pandas 加载数据
df = load_df("Superstore Dataset.xlsx")
df.columns = [col.replace(" ", "_").replace("-", "_") for col in df.columns]
df = df.iloc[:, 1:]

# 设置数据源
data = pbi.set_source(df)


def slicers():
    with pbi.box().set_gridArea("scs"):
        pbi.add_slicer(data["Segment"])
        pbi.add_slicer(data["Category"])
        pbi.add_slicer(data["Region"])
        pbi.add_slicer(data["Ship_Mode"]).set_title("Ship Mode")


# 卡片的样式
card_box_styles = styles.boxShadow.shadow_md + styles.textSize.text_xl


# 卡片
def card(grid_tag: str, value, pos: str, title: str):
    with pbi.box("center").set_gridArea(grid_tag) as box:
        box + card_box_styles
        pbi.add_text(f"{value} {pos}")
        pbi.add_text(title)


# 顶部所有卡片
def cards():
    total_data = pbi.set_dataView(
        f"""select 
        round(sum(Sales)/1000,2) as Sales,
        round(avg(Sales)/1000,2) as avg_Sales from {data}""",
    )

    card("card1", total_data["Sales"], "千", "总销售额")
    card("card2", total_data["avg_Sales"], "千", "平均销售额")


def bar1(grid_tag: str):
    segment_summary = pbi.set_dataView(
        f"select Segment,round(sum(Sales),2) as Sales from {data} group by Segment"
    )

    opts = easy_chart.make_bar(segment_summary, x="Segment", y="Sales").reverse_axis()

    pbi.add_echart(opts).set_gridArea(grid_tag).hover_filter(
        "x", data, "Segment"
    ).set_height("15em")


def bar2(grid_tag: str):
    segment_summary = pbi.set_dataView(
        f"select Category,round(sum(Sales),2) as Sales from {data} group by Category"
    )

    opts = easy_chart.make_bar(segment_summary, x="Category", y="Sales").reverse_axis()

    pbi.add_echart(opts).set_gridArea(grid_tag).hover_filter(
        "x", data, "Category"
    ).set_height("15em")


def bar_charts():
    bar1("bar1")
    bar2("bar2")


grid = """
    scs card1 . card2 .
    scs bar1 bar1 bar2 bar2
    scs bar1 bar1 bar2 bar2
    scs table table table table
    scs table table table table
"""


with pbi.gridBox(grid):
    slicers()
    cards()
    bar_charts()

    pbi.add_table(data).set_gridArea("table")


pbi.to_html("test.html")
